import pymysql
import requests
from fake_useragent import UserAgent
from lxml import etree

UA = UserAgent().random
headers = {
    'User-Agent':UA
}
url ="https://movie.douban.com/top250"
res = requests.get(url,headers=headers)
res.encoding ='utf-8'

html = etree.HTML(res.text)  # 标签化
name = html.xpath('// div[@class="hd"] /a/span[1]/text()')
score = html.xpath('// div[@class="star"] /span[2]/text()')

zh = list(zip(name,score))
# print(zh)

# 连接到数据库
conn = pymysql.connect(host='localhost', user='root', password='151612', database='Python')
# 创建游标
cursor = conn.cursor()
# 创建表（如果表不存在）
cursor.execute('''CREATE TABLE IF NOT EXISTS MOV (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(255), score INT)''')
# 插入数据到表中
for item in zh:
    cursor.execute("INSERT INTO MOV (name, score) VALUES (%s, %s)", item)
# 查看插入后的结果
cursor.execute("SELECT * FROM MOV")
results = cursor.fetchall()
for row in results:
    print(row)
# 提交事务
conn.commit()
# 关闭连接
conn.close()
